MedicalSystem DML

CREATE TEMP TABLE tmp_names (rn INT PRIMARY KEY, name TEXT, gender TEXT);
CREATE TEMP TABLE tmp_surnames (rn INT PRIMARY KEY, surname TEXT);

INSERT INTO tmp_names (rn, name, gender) VALUES
(1,'James','MALE'),(2,'John','MALE'),(3,'Robert','MALE'),(4,'Michael','MALE'),(5,'William','MALE'),
(6,'David','MALE'),(7,'Richard','MALE'),(8,'Joseph','MALE'),(9,'Thomas','MALE'),(10,'Charles','MALE'),
(11,'Christopher','MALE'),(12,'Daniel','MALE'),(13,'Matthew','MALE'),(14,'Anthony','MALE'),(15,'Mark','MALE'),
(16,'Donald','MALE'),(17,'Steven','MALE'),(18,'Paul','MALE'),(19,'Andrew','MALE'),(20,'Kenneth','MALE'),
(21,'Kevin','MALE'),(22,'Brian','MALE'),(23,'George','MALE'),(24,'Timothy','MALE'),(25,'Ronald','MALE'),
(26,'Edward','MALE'),(27,'Jason','MALE'),(28,'Jeffrey','MALE'),(29,'Ryan','MALE'),(30,'Jacob','MALE'),
(31,'Gary','MALE'),(32,'Nicholas','MALE'),(33,'Eric','MALE'),(34,'Jonathan','MALE'),(35,'Stephen','MALE'),
(36,'Larry','MALE'),(37,'Justin','MALE'),(38,'Scott','MALE'),(39,'Brandon','MALE'),(40,'Benjamin','MALE'),
(41,'Samuel','MALE'),(42,'Raymond','MALE'),(43,'Gregory','MALE'),(44,'Frank','MALE'),(45,'Alexander','MALE'),
(46,'Patrick','MALE'),(47,'Jack','MALE'),(48,'Dennis','MALE'),(49,'Jerry','MALE'),(50,'Tyler','MALE'),
(51,'Mary','FEMALE'),(52,'Patricia','FEMALE'),(53,'Jennifer','FEMALE'),(54,'Linda','FEMALE'),(55,'Barbara','FEMALE'),
(56,'Elizabeth','FEMALE'),(57,'Susan','FEMALE'),(58,'Jessica','FEMALE'),(59,'Sarah','FEMALE'),(60,'Karen','FEMALE'),
(61,'Lisa','FEMALE'),(62,'Nancy','FEMALE'),(63,'Betty','FEMALE'),(64,'Margaret','FEMALE'),(65,'Sandra','FEMALE'),
(66,'Ashley','FEMALE'),(67,'Dorothy','FEMALE'),(68,'Kimberly','FEMALE'),(69,'Emily','FEMALE'),(70,'Donna','FEMALE'),
(71,'Michelle','FEMALE'),(72,'Carol','FEMALE'),(73,'Amanda','FEMALE'),(74,'Melissa','FEMALE'),(75,'Deborah','FEMALE'),
(76,'Stephanie','FEMALE'),(77,'Rebecca','FEMALE'),(78,'Sharon','FEMALE'),(79,'Laura','FEMALE'),(80,'Cynthia','FEMALE'),
(81,'Kathleen','FEMALE'),(82,'Amy','FEMALE'),(83,'Angela','FEMALE'),(84,'Shirley','FEMALE'),(85,'Anna','FEMALE'),
(86,'Brenda','FEMALE'),(87,'Pamela','FEMALE'),(88,'Emma','FEMALE'),(89,'Nicole','FEMALE'),(90,'Helen','FEMALE'),
(91,'Samantha','FEMALE'),(92,'Katherine','FEMALE'),(93,'Christine','FEMALE'),(94,'Rachel','FEMALE'),(95,'Lauren','FEMALE'),
(96,'Kelly','FEMALE'),(97,'Christina','FEMALE'),(98,'Joan','FEMALE'),(99,'Evelyn','FEMALE'),(100,'Judith','FEMALE');

INSERT INTO tmp_surnames (rn, surname) VALUES
(1,'Smith'),(2,'Johnson'),(3,'Williams'),(4,'Brown'),(5,'Jones'),
(6,'Garcia'),(7,'Miller'),(8,'Davis'),(9,'Rodriguez'),(10,'Martinez'),
(11,'Hernandez'),(12,'Lopez'),(13,'Gonzalez'),(14,'Wilson'),(15,'Anderson'),
(16,'Thomas'),(17,'Taylor'),(18,'Moore'),(19,'Jackson'),(20,'Martin'),
(21,'Lee'),(22,'Perez'),(23,'Thompson'),(24,'White'),(25,'Harris'),
(26,'Sanchez'),(27,'Clark'),(28,'Ramirez'),(29,'Lewis'),(30,'Robinson'),
(31,'Walker'),(32,'Young'),(33,'Allen'),(34,'King'),(35,'Wright'),
(36,'Scott'),(37,'Torres'),(38,'Nguyen'),(39,'Hill'),(40,'Flores'),
(41,'Green'),(42,'Adams'),(43,'Nelson'),(44,'Baker'),(45,'Hall'),
(46,'Rivera'),(47,'Campbell'),(48,'Mitchell'),(49,'Carter'),(50,'Roberts'),
(51,'Gomez'),(52,'Phillips'),(53,'Evans'),(54,'Turner'),(55,'Diaz'),
(56,'Parker'),(57,'Cruz'),(58,'Edwards'),(59,'Collins'),(60,'Reyes'),
(61,'Stewart'),(62,'Morris'),(63,'Morales'),(64,'Murphy'),(65,'Cook'),
(66,'Rogers'),(67,'Gutierrez'),(68,'Ortiz'),(69,'Morgan'),(70,'Cooper'),
(71,'Peterson'),(72,'Bailey'),(73,'Reed'),(74,'Kelly'),(75,'Howard'),
(76,'Ramos'),(77,'Kim'),(78,'Cox'),(79,'Ward'),(80,'Richardson'),
(81,'Watson'),(82,'Brooks'),(83,'Chavez'),(84,'Wood'),(85,'James'),
(86,'Bennett'),(87,'Gray'),(88,'Mendoza'),(89,'Ruiz'),(90,'Hughes'),
(91,'Price'),(92,'Alvarez'),(93,'Castillo'),(94,'Sanders'),(95,'Patel'),
(96,'Myers'),(97,'Long'),(98,'Ross'),(99,'Foster'),(100,'Jimenez');

INSERT INTO Specialization (spec_id, spec_name) VALUES
(1,'Cardiology'),(2,'Neurology'),(3,'Orthopedics'),(4,'Pediatrics'),
(5,'Dermatology'),(6,'Oncology'),(7,'Gastroenterology'),(8,'Pulmonology'),
(9,'Endocrinology'),(10,'Nephrology'),(11,'Ophthalmology'),(12,'Psychiatry'),
(13,'Rheumatology'),(14,'Urology'),(15,'Hematology'),(16,'Infectious Disease'),
(17,'Emergency Medicine'),(18,'General Surgery'),(19,'Radiology'),(20,'Anesthesiology');

INSERT INTO Hospital (hospital_id, hospital_address) VALUES
(1,'100 Medical Center Dr, New York, NY 10001'),
(2,'200 Healthcare Blvd, Los Angeles, CA 90001'),
(3,'300 Wellness Ave, Chicago, IL 60601'),
(4,'400 Hospital Lane, Houston, TX 77001'),
(5,'500 Care Street, Phoenix, AZ 85001'),
(6,'600 Health Pkwy, Philadelphia, PA 19101'),
(7,'700 Mercy Blvd, San Antonio, TX 78201'),
(8,'800 General Ave, San Diego, CA 92101'),
(9,'900 University Dr, Dallas, TX 75201'),
(10,'1000 Memorial Blvd, San Jose, CA 95101'),
(11,'101 St. Luke Rd, Austin, TX 73301'),
(12,'202 Providence Ave, Jacksonville, FL 32201'),
(13,'303 Cedars Blvd, Fort Worth, TX 76101'),
(14,'404 Baptist Dr, Columbus, OH 43201'),
(15,'505 Methodist Ln, Charlotte, NC 28201'),
(16,'606 Presbyterian Ave, Indianapolis, IN 46201'),
(17,'707 Sinai Blvd, San Francisco, CA 94101'),
(18,'808 Covenant Dr, Seattle, WA 98101'),
(19,'809 Harbor Blvd, Denver, CO 80201'),
(20,'810 Valley Rd, Nashville, TN 37201'),
(21,'811 Riverside Dr, Oklahoma City, OK 73101'),
(22,'812 Lakeview Ave, El Paso, TX 79901'),
(23,'813 Hillcrest Blvd, Washington, DC 20001'),
(24,'814 Sunrise Pkwy, Las Vegas, NV 89101'),
(25,'815 Eastside Dr, Louisville, KY 40201'),
(26,'816 Northside Ave, Baltimore, MD 21201'),
(27,'817 Westpark Blvd, Milwaukee, WI 53201'),
(28,'818 Southgate Dr, Albuquerque, NM 87101'),
(29,'819 Central Ave, Tucson, AZ 85701'),
(30,'820 Midtown Blvd, Fresno, CA 93701'),
(31,'821 Downtown Dr, Sacramento, CA 95801'),
(32,'822 Uptown Ave, Mesa, AZ 85201'),
(33,'823 Fairview Rd, Kansas City, MO 64101'),
(34,'824 Greenfield Dr, Atlanta, GA 30301'),
(35,'825 Brookside Ave, Omaha, NE 68101'),
(36,'826 Clearwater Blvd, Colorado Springs, CO 80901'),
(37,'827 Highpoint Dr, Raleigh, NC 27601'),
(38,'828 Lakewood Ave, Long Beach, CA 90801'),
(39,'829 Pinecrest Rd, Virginia Beach, VA 23451'),
(40,'830 Oakdale Blvd, Minneapolis, MN 55401'),
(41,'831 Maplewood Dr, Tampa, FL 33601'),
(42,'832 Elmwood Ave, New Orleans, LA 70112'),
(43,'833 Cedarbrook Rd, Arlington, TX 76001'),
(44,'834 Willowbrook Blvd, Wichita, KS 67201'),
(45,'835 Birchwood Dr, Bakersfield, CA 93301'),
(46,'836 Rosewood Ave, Aurora, CO 80010'),
(47,'847 Magnolia Blvd, Anaheim, CA 92801'),
(48,'848 Chestnut Dr, Santa Ana, CA 92701'),
(49,'849 Walnut Ave, Corpus Christi, TX 78401'),
(50,'850 Hawthorn Rd, Riverside, CA 92501'),
(51,'851 Poplar Blvd, Lexington, KY 40501'),
(52,'852 Sycamore Dr, St. Louis, MO 63101'),
(53,'853 Ash Tree Ave, Pittsburgh, PA 15201'),
(54,'854 Redwood Blvd, Anchorage, AK 99501'),
(55,'855 Spruce Dr, Stockton, CA 95201'),
(56,'856 Cypress Ave, Cincinnati, OH 45201'),
(57,'857 Juniper Rd, St. Paul, MN 55101'),
(58,'858 Sequoia Blvd, Toledo, OH 43601'),
(59,'859 Aspen Dr, Greensboro, NC 27401'),
(60,'860 Douglas Ave, Newark, NJ 07101'),
(61,'861 Fir Tree Rd, Plano, TX 75023'),
(62,'862 Hemlock Blvd, Henderson, NV 89002'),
(63,'863 Laurel Dr, Lincoln, NE 68501'),
(64,'864 Boxwood Ave, Buffalo, NY 14201'),
(65,'865 Holly Rd, Fort Wayne, IN 46801'),
(66,'866 Ivy Blvd, Jersey City, NJ 07302'),
(67,'867 Moss Dr, Chula Vista, CA 91910'),
(68,'868 Fern Ave, Orlando, FL 32801'),
(69,'869 Sage Rd, St. Petersburg, FL 33701'),
(70,'870 Thyme Blvd, Laredo, TX 78041'),
(71,'871 Basil Dr, Madison, WI 53701'),
(72,'872 Rosemary Ave, Norfolk, VA 23501'),
(73,'873 Lavender Rd, Durham, NC 27701'),
(74,'874 Mint Blvd, Lubbock, TX 79401'),
(75,'875 Clover Dr, Winston-Salem, NC 27101'),
(76,'876 Daisy Ave, Garland, TX 75040'),
(77,'877 Lily Rd, Glendale, AZ 85301'),
(78,'878 Tulip Blvd, Hialeah, FL 33010'),
(79,'879 Orchid Dr, Reno, NV 89501'),
(80,'880 Iris Ave, Baton Rouge, LA 70801'),
(81,'881 Violet Rd, Irvine, CA 92602'),
(82,'882 Pansy Blvd, Chesapeake, VA 23320'),
(83,'883 Peony Dr, Scottsdale, AZ 85251'),
(84,'884 Aster Ave, North Las Vegas, NV 89030'),
(85,'885 Begonia Rd, Fremont, CA 94536'),
(86,'886 Camellia Blvd, Gilbert, AZ 85296'),
(87,'887 Dahlia Dr, San Bernardino, CA 92401'),
(88,'888 Gardenia Ave, Birmingham, AL 35203'),
(89,'889 Hibiscus Rd, Rochester, NY 14601'),
(90,'890 Jasmine Blvd, Spokane, WA 99201'),
(91,'891 Zinnia Dr, Des Moines, IA 50301'),
(92,'892 Marigold Ave, Modesto, CA 95351'),
(93,'893 Sunflower Rd, Fayetteville, NC 28301'),
(94,'894 Buttercup Blvd, Tacoma, WA 98401'),
(95,'895 Bluebell Dr, Oxnard, CA 93030'),
(96,'896 Carnation Ave, Fontana, CA 92335'),
(97,'897 Snapdragon Rd, Moreno Valley, CA 92553'),
(98,'898 Foxglove Blvd, Glendale, CA 91201'),
(99,'899 Hollyhock Dr, Huntington Beach, CA 92647'),
(100,'900 Cosmos Ave, Montgomery, AL 36101'),
(101,'901 Aster Rd, Akron, OH 44301'),
(102,'902 Lilac Blvd, Little Rock, AR 72201'),
(103,'903 Wisteria Dr, Columbus, GA 31901'),
(104,'904 Forsythia Ave, Grand Rapids, MI 49501'),
(105,'905 Magnolia Rd, Salt Lake City, UT 84101'),
(106,'906 Azalea Blvd, Huntsville, AL 35801'),
(107,'907 Bougainvillea Dr, Knoxville, TN 37901'),
(108,'908 Verbena Ave, Worcester, MA 01601'),
(109,'909 Lantana Rd, Brownsville, TX 78520'),
(110,'910 Impatiens Blvd, Santa Clarita, CA 91380'),
(111,'911 Primrose Dr, Providence, RI 02901'),
(112,'912 Yarrow Ave, Garden Grove, CA 92840'),
(113,'913 Valerian Rd, Oceanside, CA 92054'),
(114,'914 Chamomile Blvd, Chattanooga, TN 37401'),
(115,'915 Echinacea Dr, Fort Lauderdale, FL 33301'),
(116,'916 Goldenrod Ave, Rancho Cucamonga, CA 91730'),
(117,'917 St Johns Wort Rd, Santa Rosa, CA 95401'),
(118,'918 Elderflower Blvd, Tempe, AZ 85281'),
(119,'919 Feverfew Dr, Cape Coral, FL 33990'),
(120,'920 Dandelion Ave, Sioux Falls, SD 57101');

INSERT INTO Department (department_id, department_name, spec_id)
SELECT
    s.i,
    sp.spec_name || ' Department',
    sp.spec_id
FROM generate_series(1, 150) AS s(i)
JOIN Specialization sp ON sp.spec_id = ((s.i - 1) % 20) + 1;

UPDATE Department SET department_name = 'Emergency Department'     WHERE department_id = 17;
UPDATE Department SET department_name = 'Cardiac ICU'              WHERE department_id = 21;
UPDATE Department SET department_name = 'Surgical ICU'             WHERE department_id = 38;
UPDATE Department SET department_name = 'Pediatric ICU'            WHERE department_id = 44;
UPDATE Department SET department_name = 'Oncology Day Care'        WHERE department_id = 66;
UPDATE Department SET department_name = 'Neuro ICU'                WHERE department_id = 82;
UPDATE Department SET department_name = 'Radiology & Imaging'      WHERE department_id = 99;
UPDATE Department SET department_name = 'General Surgery Suite'    WHERE department_id = 118;

INSERT INTO Doctor (doctor_id, spec_id, first_name, last_name, phone, email)
SELECT
    s.i,
    (s.i % 20) + 1,
    n.name,
    sn.surname,
    '+1' || lpad((3000000000 + s.i)::TEXT, 10, '0'),
    lower(n.name) || '.' || lower(sn.surname) || s.i::TEXT || '@hospital.com'
FROM generate_series(1, 3000) AS s(i)
JOIN tmp_names    n  ON n.rn  = (s.i % 100) + 1
JOIN tmp_surnames sn ON sn.rn = (s.i % 100) + 1;

INSERT INTO Doctor_department (
    doctor_dept_id, doctor_id, department_id,
    employment_type, date_from, date_to
)
SELECT
    s.i,
    s.i,
    (s.i % 150) + 1,
    CASE (s.i % 4)
        WHEN 0 THEN 'FULL_TIME'  WHEN 1 THEN 'PART_TIME'
        WHEN 2 THEN 'CONSULTANT' ELSE       'RESIDENT'
    END,
    (CURRENT_DATE - ((s.i % 3650) * INTERVAL '1 day'))::DATE,
    CASE WHEN s.i % 7 = 0
         THEN (CURRENT_DATE - ((s.i % 365) * INTERVAL '1 day'))::DATE
         ELSE NULL
    END
FROM generate_series(1, 3000) AS s(i);

INSERT INTO Patient (
    patient_id, first_name, last_name,
    date_of_birth, gender, phone, email, address
)
SELECT
    s.i,
    n.name,
    sn.surname,
    (CURRENT_DATE - ((s.i % 29200) * INTERVAL '1 day'))::DATE,
    n.gender,
    '+1' || lpad((2000000000 + s.i)::TEXT, 10, '0'),
    lower(n.name) || '.' || lower(sn.surname) || s.i::TEXT || '@email.com',
    (s.i % 9999 + 1)::TEXT || ' ' ||
        CASE (s.i % 10)
            WHEN 0 THEN 'Main St'    WHEN 1 THEN 'Oak Ave'
            WHEN 2 THEN 'Maple Dr'   WHEN 3 THEN 'Cedar Blvd'
            WHEN 4 THEN 'Pine Rd'    WHEN 5 THEN 'Elm St'
            WHEN 6 THEN 'Park Ave'   WHEN 7 THEN 'Lake Dr'
            WHEN 8 THEN 'River Rd'   ELSE       'Highland Ave'
        END
FROM generate_series(1, 2000000) AS s(i)
JOIN tmp_names    n  ON n.rn  = (s.i % 100) + 1
JOIN tmp_surnames sn ON sn.rn = (s.i % 100) + 1;

INSERT INTO ICD (icd_id, code, description) VALUES
(1,'I10','Essential hypertension'),
(2,'E11','Type 2 diabetes mellitus'),
(3,'J18.9','Pneumonia, unspecified'),
(4,'M54.5','Low back pain'),
(5,'J06.9','Acute upper respiratory infection'),
(6,'E78.5','Hyperlipidemia, unspecified'),
(7,'G43.9','Migraine, unspecified'),
(8,'F32.9','Major depressive disorder'),
(9,'K21.0','Gastro-esophageal reflux disease'),
(10,'I25.10','Atherosclerotic heart disease'),
(11,'N39.0','Urinary tract infection'),
(12,'J45.9','Asthma, unspecified'),
(13,'M17.9','Osteoarthritis of knee'),
(14,'E03.9','Hypothyroidism, unspecified'),
(15,'F41.1','Generalized anxiety disorder'),
(16,'I50.9','Heart failure, unspecified'),
(17,'C34.9','Malignant neoplasm of lung'),
(18,'N18.3','Chronic kidney disease stage 3'),
(19,'B97.89','Viral infection unspecified'),
(20,'Z87.891','History of nicotine dependence'),
(21,'K92.1','Melena'),
(22,'S72.001','Fracture of femur neck'),
(23,'G20','Parkinson disease'),
(24,'G35','Multiple sclerosis'),
(25,'C50.9','Malignant neoplasm of breast'),
(26,'C18.9','Malignant neoplasm of colon'),
(27,'I21.9','Acute myocardial infarction'),
(28,'I63.9','Cerebral infarction unspecified'),
(29,'E11.65','Type 2 diabetes with hyperglycemia'),
(30,'M79.3','Panniculitis unspecified'),
(31,'L40.0','Psoriasis vulgaris'),
(32,'K57.30','Diverticulosis of large intestine'),
(33,'Z00.00','General adult medical examination'),
(34,'J20.9','Acute bronchitis unspecified'),
(35,'R05','Cough'),
(36,'R51','Headache'),
(37,'R50.9','Fever unspecified'),
(38,'N20.0','Calculus of kidney'),
(39,'K35.80','Acute appendicitis'),
(40,'H35.30','Macular degeneration'),
(41,'I48.91','Unspecified atrial fibrillation'),
(42,'E10','Type 1 diabetes mellitus'),
(43,'J44.1','COPD with acute exacerbation'),
(44,'M05.79','Rheumatoid arthritis with rheumatoid factor'),
(45,'N17.9','Acute kidney failure unspecified'),
(46,'K70.30','Alcoholic cirrhosis of liver without ascites'),
(47,'G40.909','Epilepsy unspecified'),
(48,'F20.9','Schizophrenia unspecified'),
(49,'C61','Malignant neoplasm of prostate'),
(50,'C92.00','Acute myeloblastic leukemia'),
(51,'D50.9','Iron deficiency anemia unspecified'),
(52,'E55.9','Vitamin D deficiency unspecified'),
(53,'M81.0','Age-related osteoporosis'),
(54,'H25.9','Unspecified age-related cataract'),
(55,'H40.9','Unspecified glaucoma'),
(56,'J30.9','Allergic rhinitis unspecified'),
(57,'K80.20','Calculus of gallbladder without cholecystitis'),
(58,'K86.1','Other chronic pancreatitis'),
(59,'L20.9','Atopic dermatitis unspecified'),
(60,'M10.9','Gout unspecified'),
(61,'N40.0','Benign prostatic hyperplasia without LUTS'),
(62,'N83.20','Unspecified ovarian cyst'),
(63,'O24.419','Unspecified diabetes mellitus in pregnancy'),
(64,'P07.30','Preterm newborn unspecified weeks'),
(65,'Q21.0','Ventricular septal defect'),
(66,'R00.0','Tachycardia unspecified'),
(67,'R06.00','Dyspnea unspecified'),
(68,'R10.9','Unspecified abdominal pain'),
(69,'R55','Syncope and collapse'),
(70,'S06.0X0A','Concussion without loss of consciousness'),
(71,'T14.90','Injury unspecified'),
(72,'Z23','Encounter for immunization'),
(73,'Z34.00','Encounter for supervision of normal pregnancy'),
(74,'Z51.11','Encounter for antineoplastic chemotherapy'),
(75,'Z79.01','Long-term use of anticoagulants'),
(76,'I11.9','Hypertensive heart disease without heart failure'),
(77,'I20.9','Angina pectoris unspecified'),
(78,'I26.99','Other pulmonary embolism without acute cor pulmonale'),
(79,'I35.0','Nonrheumatic aortic stenosis'),
(80,'I42.9','Cardiomyopathy unspecified'),
(81,'I70.209','Unspecified atherosclerosis of native arteries of extremities'),
(82,'I80.209','Phlebitis and thrombophlebitis of unspecified deep vessels'),
(83,'J84.10','Pulmonary fibrosis unspecified'),
(84,'K50.90','Crohns disease of small intestine without complications'),
(85,'K51.90','Ulcerative colitis unspecified without complications'),
(86,'K72.90','Hepatic failure unspecified without coma'),
(87,'L03.90','Cellulitis unspecified'),
(88,'M06.9','Rheumatoid arthritis unspecified'),
(89,'M43.6','Torticollis'),
(90,'M51.16','Intervertebral disc degeneration lumbar region'),
(91,'N02.9','Recurrent hematuria unspecified morphologic changes'),
(92,'N04.9','Nephrotic syndrome with unspecified morphologic changes'),
(93,'N10','Acute pyelonephritis'),
(94,'N25.0','Renal osteodystrophy'),
(95,'N30.00','Acute cystitis without hematuria'),
(96,'N41.0','Acute prostatitis'),
(97,'C16.9','Malignant neoplasm of stomach unspecified'),
(98,'C22.0','Liver cell carcinoma'),
(99,'C25.9','Malignant neoplasm of pancreas unspecified'),
(100,'C64.9','Malignant neoplasm of unspecified kidney'),
(101,'C67.9','Malignant neoplasm of bladder unspecified'),
(102,'C73','Malignant neoplasm of thyroid gland'),
(103,'C81.90','Hodgkin lymphoma unspecified'),
(104,'C85.90','Non-Hodgkin lymphoma unspecified'),
(105,'C90.00','Multiple myeloma not having achieved remission'),
(106,'D18.00','Hemangioma unspecified site'),
(107,'D25.9','Leiomyoma of uterus unspecified'),
(108,'D35.00','Benign neoplasm of adrenal gland'),
(109,'E05.90','Thyrotoxicosis unspecified without thyrotoxic crisis'),
(110,'E06.9','Thyroiditis unspecified'),
(111,'E13.9','Other specified diabetes mellitus without complications'),
(112,'E21.0','Primary hyperparathyroidism'),
(113,'E22.0','Acromegaly and pituitary gigantism'),
(114,'E27.1','Primary adrenocortical insufficiency'),
(115,'E66.9','Obesity unspecified'),
(116,'F06.30','Mood disorder due to known physiological condition'),
(117,'F10.20','Alcohol dependence uncomplicated'),
(118,'F31.9','Bipolar disorder unspecified'),
(119,'F40.10','Social phobia unspecified'),
(120,'F43.10','Post-traumatic stress disorder unspecified'),
(121,'F50.9','Eating disorder unspecified'),
(122,'F60.3','Borderline personality disorder'),
(123,'G00.9','Bacterial meningitis unspecified'),
(124,'G30.9','Alzheimers disease unspecified'),
(125,'G45.9','Transient cerebral ischemic attack unspecified'),
(126,'G47.00','Insomnia unspecified'),
(127,'G51.0','Bells palsy'),
(128,'G54.2','Cervical root disorders'),
(129,'G62.9','Polyneuropathy unspecified'),
(130,'G80.9','Cerebral palsy unspecified'),
(131,'H10.9','Unspecified conjunctivitis'),
(132,'H16.9','Unspecified keratitis'),
(133,'H26.9','Unspecified cataract'),
(134,'H35.00','Unspecified background retinopathy'),
(135,'H52.4','Presbyopia'),
(136,'H61.90','Unspecified disorder of external ear'),
(137,'H65.9','Unspecified nonsuppurative otitis media'),
(138,'H72.90','Unspecified perforation of tympanic membrane'),
(139,'H81.10','Benign paroxysmal vertigo unspecified ear'),
(140,'H83.9','Unspecified disease of inner ear'),
(141,'J01.90','Acute sinusitis unspecified'),
(142,'J02.9','Acute pharyngitis unspecified'),
(143,'J03.90','Acute tonsillitis unspecified'),
(144,'J31.0','Chronic rhinitis'),
(145,'J32.9','Chronic sinusitis unspecified'),
(146,'J35.01','Chronic tonsillitis'),
(147,'J38.00','Paralysis of vocal cords and larynx unspecified'),
(148,'K04.0','Pulpitis'),
(149,'K08.109','Complete loss of teeth unspecified cause'),
(150,'K11.20','Sialoadenitis unspecified'),
(151,'K22.0','Achalasia of cardia'),
(152,'K25.9','Gastric ulcer unspecified'),
(153,'K29.70','Gastritis unspecified without bleeding'),
(154,'K40.90','Unilateral inguinal hernia without obstruction or gangrene'),
(155,'K43.9','Ventral hernia without obstruction or gangrene'),
(156,'K55.9','Vascular disorder of intestine unspecified'),
(157,'K59.00','Constipation unspecified'),
(158,'K59.1','Functional diarrhea'),
(159,'K63.5','Polyp of colon'),
(160,'K74.60','Unspecified cirrhosis of liver'),
(161,'L08.9','Local infection of skin unspecified'),
(162,'L10.0','Pemphigus vulgaris'),
(163,'L23.9','Allergic contact dermatitis unspecified'),
(164,'L30.9','Dermatitis unspecified'),
(165,'L43.9','Lichen planus unspecified'),
(166,'L50.9','Urticaria unspecified'),
(167,'L60.0','Ingrowing nail'),
(168,'L70.0','Acne vulgaris'),
(169,'L71.0','Perioral dermatitis'),
(170,'L72.0','Epidermal cyst'),
(171,'M00.9','Pyogenic arthritis unspecified'),
(172,'M12.9','Arthropathy unspecified'),
(173,'M16.9','Osteoarthritis of hip unspecified'),
(174,'M19.90','Unspecified osteoarthritis unspecified site'),
(175,'M20.10','Hallux valgus unspecified foot'),
(176,'M23.200','Derangement of unspecified meniscus'),
(177,'M25.50','Pain in unspecified joint'),
(178,'M32.9','Systemic lupus erythematosus unspecified'),
(179,'M34.9','Systemic sclerosis unspecified'),
(180,'M35.9','Systemic involvement of connective tissue unspecified'),
(181,'N13.30','Unspecified hydronephrosis'),
(182,'N21.0','Calculus in bladder'),
(183,'N35.9','Urethral stricture unspecified'),
(184,'N43.3','Hydrocele unspecified'),
(185,'N45.1','Orchitis'),
(186,'N48.29','Other priapism'),
(187,'N60.01','Solitary cyst of right breast'),
(188,'N63.0','Unspecified lump in unspecified breast'),
(189,'N76.0','Acute vaginitis'),
(190,'N80.0','Endometriosis of uterus'),
(191,'N94.6','Dysmenorrhoea unspecified'),
(192,'N95.1','Menopausal and female climacteric states'),
(193,'O03.9','Complete or unspecified spontaneous abortion without complication'),
(194,'O09.90','Supervision of high-risk pregnancy unspecified'),
(195,'O14.00','Mild to moderate preeclampsia unspecified trimester'),
(196,'O42.00','Premature rupture of membranes onset of labor within 24hrs'),
(197,'O60.10','Preterm labor without delivery unspecified trimester'),
(198,'O80','Encounter for full-term uncomplicated delivery'),
(199,'P00.0','Newborn affected by maternal hypertensive disorders'),
(200,'Z76.89','Persons encountering health services in other specified circumstances');

INSERT INTO Drug_producers (drug_prod_id, address, producer_name, country_origin, website, phone) VALUES
(1,'100 Pharma Way, Basel','Novartis AG','Switzerland','www.novartis.com','+41612241111'),
(2,'1 Infinity Dr, New York NY','Pfizer Inc.','USA','www.pfizer.com','+12125732323'),
(3,'200 Merck Dr, Kenilworth NJ','Merck & Co.','USA','www.merck.com','+19082404000'),
(4,'25 New North Place, London','GlaxoSmithKline plc','UK','www.gsk.com','+442089904477'),
(5,'1 Johnson Dr, New Brunswick NJ','Johnson & Johnson','USA','www.jnj.com','+17325242455'),
(6,'500 Arcola Rd, Collegeville PA','AstraZeneca','UK','www.astrazeneca.com','+18004562244'),
(7,'51368 Leverkusen, Germany','Bayer AG','Germany','www.bayer.com','+492143050'),
(8,'1 Amgen Center Dr, Thousand Oaks CA','Amgen Inc.','USA','www.amgen.com','+18054471000'),
(9,'4070 Basel, Switzerland','F. Hoffmann-La Roche AG','Switzerland','www.roche.com','+41616881111'),
(10,'22 Elm St, Whippany NJ','Sanofi S.A.','France','www.sanofi.com','+18003814884'),
(11,'Grenzacherstr 124, Basel','Novartis Pharma AG','Switzerland','www.novartis.com','+41613245678'),
(12,'235 E 42nd St, New York NY','Pfizer Global R&D','USA','www.pfizer.com','+12125559876'),
(13,'One Merck Dr, Whitehouse Station NJ','Merck Sharp & Dohme','USA','www.msd.com','+19082401234'),
(14,'980 Great West Rd, Brentford','GSK Consumer Healthcare','UK','www.gsk.com','+441895523456'),
(15,'410 George St, New Brunswick NJ','Janssen Pharmaceutica','Belgium','www.janssen.com','+13217247890'),
(16,'1800 Concord Pike, Wilmington DE','AstraZeneca US','USA','www.astrazeneca.com','+13028867890'),
(17,'100 Bayer Blvd, Whippany NJ','Bayer HealthCare','USA','www.bayer.com','+19735945000'),
(18,'One Amgen Center Dr, Thousand Oaks CA','Amgen Biologics','USA','www.amgen.com','+18054476789'),
(19,'1 DNA Way, South San Francisco CA','Genentech Inc.','USA','www.gene.com','+16503254321'),
(20,'Sandoz AG, Basel','Sandoz International GmbH','Germany','www.sandoz.com','+498024476000'),
(21,'Industriestr 25, Leverkusen','Bayer Vital GmbH','Germany','www.bayervital.de','+492144752345'),
(22,'54 rue La Boetie, Paris','Ipsen Pharma','France','www.ipsen.com','+33158336500'),
(23,'1950 Lake Park Dr SE, Smyrna GA','Hikma Pharmaceuticals','Jordan','www.hikma.com','+14046810200'),
(24,'400 Somerset Corp Blvd, Bridgewater NJ','Sanofi Genzyme','USA','www.sanofi.com','+19087823000'),
(25,'Global Res Ctr, Cambridge UK','AstraZeneca R&D','UK','www.astrazeneca.com','+441223420000'),
(26,'Hanauer Landstr 526, Frankfurt','Aventis Pharma','Germany','www.aventis.com','+496914500'),
(27,'1 Genentech Way, South San Francisco CA','Roche Genentech','USA','www.roche.com','+16503327000'),
(28,'Bld du Triomphe, Brussels','UCB Pharma','Belgium','www.ucb.com','+3222887211'),
(29,'430 E 29th St, New York NY','Boehringer Ingelheim','Germany','www.boehringer-ingelheim.com','+12129098000'),
(30,'700 Chesterfield Pkwy, Chesterfield MO','Mallinckrodt Pharmaceuticals','USA','www.mallinckrodt.com','+16363881000'),
(31,'Takeda Global HQ, Osaka','Takeda Pharmaceutical','Japan','www.takeda.com','+81669761000'),
(32,'3-4-1 Marunouchi, Tokyo','Astellas Pharma Inc.','Japan','www.astellas.com','+81332443000'),
(33,'1-6-5 Marunouchi, Tokyo','Daiichi Sankyo Co.','Japan','www.daiichi-sankyo.com','+81352180800'),
(34,'2-1-1 Nihonbashi, Tokyo','Eisai Co. Ltd.','Japan','www.eisai.com','+81336721600'),
(35,'Sumitomo Corp, Tokyo','Sumitomo Pharma','Japan','www.sumitomo-pharma.com','+81362041000'),
(36,'1 Baxter Pkwy, Deerfield IL','Baxter International','USA','www.baxter.com','+18479481212'),
(37,'100 Abbott Park Rd, Abbott Park IL','Abbott Laboratories','USA','www.abbott.com','+18473376100'),
(38,'One Becton Dr, Franklin Lakes NJ','Becton Dickinson','USA','www.bd.com','+12014472000'),
(39,'702 Electronic Dr, Horsham PA','Teva Pharmaceuticals','Israel','www.tevapharm.com','+12153540600'),
(40,'Generics UK, Potters Bar','Mylan Laboratories','USA','www.mylan.com','+441707853000'),
(41,'East Hanover, New Jersey','Novartis Oncology','USA','www.novartisoncology.com','+18622782000'),
(42,'777 Old Saw Mill River Rd, Tarrytown NY','Regeneron Pharmaceuticals','USA','www.regeneron.com','+19144477000'),
(43,'1 DNA Way, South San Francisco CA','Biogen Inc.','USA','www.biogen.com','+16173798200'),
(44,'200 First St SW, Rochester MN','Mayo Clinic Pharmaceuticals','USA','www.mayo.edu','+15072843964'),
(45,'1209 Orange St, Wilmington DE','Incyte Corporation','USA','www.incyte.com','+13028432300'),
(46,'Industriepark Hochst, Frankfurt','Hoechst AG','Germany','www.hoechst.com','+496930520'),
(47,'Zurich, Switzerland','Lonza Group AG','Switzerland','www.lonza.com','+41617165500'),
(48,'151 Farmington Ave, Hartford CT','Cigna Pharma Services','USA','www.cigna.com','+18604501000'),
(49,'300 First Stamford Pl, Stamford CT','Purdue Pharma','USA','www.purduepharma.com','+12034888000'),
(50,'12999 E Caley Ave, Centennial CO','DaVita Inc.','USA','www.davita.com','+17203728989');

INSERT INTO ATC_code (atc_id, atc_code, description) VALUES
(1,'C09AA01','Captopril - ACE inhibitors'),
(2,'C09AA05','Ramipril - ACE inhibitors'),
(3,'C10AA01','Simvastatin - HMG CoA reductase inhibitors'),
(4,'C10AA05','Atorvastatin - HMG CoA reductase inhibitors'),
(5,'A10BA02','Metformin - Biguanides'),
(6,'A10BB01','Glibenclamide - Sulfonylureas'),
(7,'N02BE01','Paracetamol - Anilides'),
(8,'N02AA01','Morphine - Natural opium alkaloids'),
(9,'J01CA04','Amoxicillin - Penicillins with extended spectrum'),
(10,'J01FA09','Clarithromycin - Macrolides'),
(11,'N06AB06','Sertraline - SSRIs'),
(12,'N06AB04','Citalopram - SSRIs'),
(13,'A02BC01','Omeprazole - Proton pump inhibitors'),
(14,'A02BC02','Pantoprazole - Proton pump inhibitors'),
(15,'R03AC02','Salbutamol - Selective beta-2-adrenoreceptor agonists'),
(16,'H03AA01','Levothyroxine - Thyroid hormones'),
(17,'C07AB02','Metoprolol - Beta blocking agents selective'),
(18,'B01AC06','Aspirin - Platelet aggregation inhibitors'),
(19,'M01AE01','Ibuprofen - Propionic acid derivatives'),
(20,'N05BA01','Diazepam - Benzodiazepine derivatives'),
(21,'C09CA01','Losartan - Angiotensin II receptor blockers'),
(22,'C09CA06','Candesartan - Angiotensin II receptor blockers'),
(23,'C08CA01','Amlodipine - Dihydropyridine calcium channel blockers'),
(24,'C08CA05','Nifedipine - Dihydropyridine calcium channel blockers'),
(25,'C03CA01','Furosemide - High-ceiling diuretics'),
(26,'C03AA03','Hydrochlorothiazide - Thiazide diuretics'),
(27,'C01AA05','Digoxin - Cardiac glycosides'),
(28,'C01BD01','Amiodarone - Antiarrhythmics class III'),
(29,'B01AA03','Warfarin - Vitamin K antagonists'),
(30,'B01AF02','Rivaroxaban - Direct factor Xa inhibitors'),
(31,'A10AE04','Insulin glargine - Long-acting insulins'),
(32,'A10AB01','Insulin regular - Short-acting insulins'),
(33,'A10BH01','Sitagliptin - DPP-4 inhibitors'),
(34,'A10BJ01','Exenatide - GLP-1 receptor agonists'),
(35,'N03AX09','Lamotrigine - Antiepileptics'),
(36,'N03AF01','Carbamazepine - Antiepileptics'),
(37,'N04BA01','Levodopa - Dopaminergic agents'),
(38,'N04BC05','Pramipexole - Dopamine agonists'),
(39,'N06AX11','Mirtazapine - Antidepressants'),
(40,'N06AX16','Venlafaxine - SNRIs'),
(41,'N05AH04','Quetiapine - Diazepines antipsychotics'),
(42,'N05AX08','Risperidone - Other antipsychotics'),
(43,'J02AC01','Fluconazole - Triazole antifungals'),
(44,'J02AC02','Itraconazole - Triazole antifungals'),
(45,'J05AB01','Acyclovir - Nucleoside analogues antivirals'),
(46,'J05AE01','Saquinavir - HIV protease inhibitors'),
(47,'J01GB03','Gentamicin - Aminoglycoside antibacterials'),
(48,'J01MA02','Ciprofloxacin - Fluoroquinolone antibacterials'),
(49,'J01DC02','Cefuroxime - Second-generation cephalosporins'),
(50,'J01DD04','Ceftriaxone - Third-generation cephalosporins'),
(51,'L01AA01','Cyclophosphamide - Alkylating agents'),
(52,'L01BC05','Gemcitabine - Pyrimidine analogues'),
(53,'L01CD01','Paclitaxel - Taxanes'),
(54,'L01XC02','Rituximab - Monoclonal antibodies'),
(55,'L02BA01','Tamoxifen - Antiestrogens'),
(56,'L04AX03','Methotrexate - Other immunosuppressants'),
(57,'L04AB02','Etanercept - TNF-alpha inhibitors'),
(58,'L04AB04','Adalimumab - TNF-alpha inhibitors'),
(59,'M01AB05','Diclofenac - Acetic acid derivatives'),
(60,'M01AC06','Meloxicam - Oxicam derivatives'),
(61,'M04AA01','Allopurinol - Preparations inhibiting uric acid production'),
(62,'M05BA04','Alendronic acid - Bisphosphonates'),
(63,'R01AD05','Budesonide - Nasal corticosteroids'),
(64,'R03BA01','Beclometasone - Inhalation corticosteroids'),
(65,'R03DC03','Montelukast - Leukotriene receptor antagonists'),
(66,'R06AX13','Loratadine - Non-sedating antihistamines'),
(67,'A01AB11','Chlorhexidine - Antiseptics'),
(68,'A03FA01','Metoclopramide - Propulsives'),
(69,'A04AA01','Ondansetron - 5HT3 antagonists antiemetics'),
(70,'A06AD11','Macrogol - Osmotically acting laxatives'),
(71,'B03BA01','Cyanocobalamin - Vitamin B12'),
(72,'B03AA07','Ferrous sulphate - Iron preparations'),
(73,'D07AC01','Betamethasone - Potent topical corticosteroids'),
(74,'D07AA02','Hydrocortisone - Mild topical corticosteroids'),
(75,'G03AA07','Levonorgestrel and ethinylestradiol - Combined oral contraceptives'),
(76,'G04BD04','Oxybutynin - Urinary antispasmodics'),
(77,'H02AB07','Prednisone - Systemic glucocorticoids'),
(78,'H02AB06','Prednisolone - Systemic glucocorticoids'),
(79,'V03AE01','Polystyrene sulphonate - Potassium binders'),
(80,'V04CG30','Glucose tolerance test agents');

INSERT INTO Drug (
    product_id, drug_prod_id, atc_id, manufacturer,
    batch_number, expiry_date, unit_price, storage_condition
)
SELECT
    s.i,
    (s.i % 50) + 1,
    (s.i % 80) + 1,
    CASE (s.i % 10)
        WHEN 0 THEN 'Novartis'    WHEN 1 THEN 'Pfizer'
        WHEN 2 THEN 'Merck'       WHEN 3 THEN 'GSK'
        WHEN 4 THEN 'J&J'         WHEN 5 THEN 'AstraZeneca'
        WHEN 6 THEN 'Bayer'       WHEN 7 THEN 'Roche'
        WHEN 8 THEN 'Sanofi'      ELSE       'Teva'
    END,
    'BATCH-' || lpad(s.i::TEXT, 5, '0'),
    (CURRENT_DATE + INTERVAL '1 year' + (s.i % 730 * INTERVAL '1 day'))::DATE,
    round((0.50 + (s.i % 200))::NUMERIC, 2),
    CASE (s.i % 5)
        WHEN 0 THEN 'Room temperature 15-25C'
        WHEN 1 THEN 'Refrigerate 2-8C'
        WHEN 2 THEN 'Cool dry place'
        WHEN 3 THEN 'Protect from light'
        ELSE       'Freeze at -20C'
    END
FROM generate_series(1, 2000) AS s(i);

INSERT INTO Pharmacy (pharmacy_id, address, name, phone, license_num)
SELECT
    s.i,
    (s.i * 10)::TEXT || ' ' ||
        CASE (s.i % 15)
            WHEN 0  THEN 'Health Blvd'     WHEN 1  THEN 'Wellness Ave'
            WHEN 2  THEN 'Care St'         WHEN 3  THEN 'Medical Dr'
            WHEN 4  THEN 'Remedy Rd'       WHEN 5  THEN 'Cure Blvd'
            WHEN 6  THEN 'Restore Ave'     WHEN 7  THEN 'Renew St'
            WHEN 8  THEN 'Revive Dr'       WHEN 9  THEN 'Refresh Blvd'
            WHEN 10 THEN 'Pharmacy Ln'     WHEN 11 THEN 'Rx Dr'
            WHEN 12 THEN 'MedCenter Pkwy'  WHEN 13 THEN 'Clinic Rd'
            ELSE         'Hospital Ave'
        END || ', ' ||
        CASE (s.i % 10)
            WHEN 0 THEN 'New York, NY'       WHEN 1 THEN 'Los Angeles, CA'
            WHEN 2 THEN 'Chicago, IL'        WHEN 3 THEN 'Houston, TX'
            WHEN 4 THEN 'Phoenix, AZ'        WHEN 5 THEN 'Philadelphia, PA'
            WHEN 6 THEN 'San Antonio, TX'    WHEN 7 THEN 'San Diego, CA'
            WHEN 8 THEN 'Dallas, TX'         ELSE       'San Jose, CA'
        END,
    CASE (s.i % 10)
        WHEN 0 THEN 'MedPlus Pharmacy #'    WHEN 1 THEN 'CareFirst Pharmacy #'
        WHEN 2 THEN 'Healwell Pharmacy #'   WHEN 3 THEN 'RxCare Pharmacy #'
        WHEN 4 THEN 'HealthHub Pharmacy #'  WHEN 5 THEN 'PharmaCare Plus #'
        WHEN 6 THEN 'QuickRx Pharmacy #'    WHEN 7 THEN 'Community Pharmacy #'
        WHEN 8 THEN 'TrustRx Pharmacy #'    ELSE       'Metro Pharmacy #'
    END || s.i::TEXT,
    '+1' || lpad((4000000000 + s.i)::TEXT, 10, '0'),
    200000 + s.i
FROM generate_series(1, 150) AS s(i);

INSERT INTO Inventory (inventory_id, product_id, pharmacy_id, location, quantity)
SELECT
    ROW_NUMBER() OVER ()::INT,
    d.product_id,
    p.pharmacy_id,
    'Shelf-' || chr(65 + (d.product_id % 8)) || (d.product_id % 20 + 1)::TEXT,
    (d.product_id % 490) + 10
FROM Drug d
CROSS JOIN Pharmacy p
WHERE (d.product_id + p.pharmacy_id) % 13 = 0;

INSERT INTO Inventory_price (
    inventory_price_id, purchasing_price, referent_price,
    sale_price, date, inventory_id
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    round((d.unit_price * 0.6)::NUMERIC, 2),
    round((d.unit_price * 0.9)::NUMERIC, 2),
    round((d.unit_price * 1.3)::NUMERIC, 2),
    (CURRENT_DATE - (i.inventory_id % 730 * INTERVAL '1 day'))::DATE,
    i.inventory_id
FROM Inventory i
JOIN Drug d ON d.product_id = i.product_id;

INSERT INTO ICD (icd_id, code, description) VALUES
(1,  'I10',   'Essential (primary) hypertension'),
(2,  'E11',   'Type 2 diabetes mellitus'),
(3,  'J18.9', 'Pneumonia, unspecified'),
(4,  'M54.5', 'Low back pain'),
(5,  'J06.9', 'Acute upper respiratory infection'),
(6,  'E78.5', 'Hyperlipidemia, unspecified'),
(7,  'G43.9', 'Migraine, unspecified'),
(8,  'F32.9', 'Major depressive disorder, single episode'),
(9,  'K21.0', 'Gastro-esophageal reflux disease'),
(10, 'I25.10','Atherosclerotic heart disease'),
(11, 'N39.0', 'Urinary tract infection'),
(12, 'J45.9', 'Asthma, unspecified'),
(13, 'M17.9', 'Osteoarthritis of knee, unspecified'),
(14, 'E03.9', 'Hypothyroidism, unspecified'),
(15, 'F41.1', 'Generalized anxiety disorder'),
(16, 'I50.9', 'Heart failure, unspecified'),
(17, 'C34.9', 'Malignant neoplasm of bronchus and lung'),
(18, 'N18.3', 'Chronic kidney disease, stage 3'),
(19, 'B97.89','Other viral agents as cause of disease'),
(20, 'Z87.891','Personal history of nicotine dependence'),
(21, 'K92.1', 'Melena'),
(22, 'S72.001','Fracture of unspecified part of neck of right femur'),
(23, 'G20',   'Parkinson disease'),
(24, 'G35',   'Multiple sclerosis'),
(25, 'C50.9', 'Malignant neoplasm of breast'),
(26, 'C18.9', 'Malignant neoplasm of colon'),
(27, 'I21.9', 'Acute myocardial infarction, unspecified'),
(28, 'I63.9', 'Cerebral infarction, unspecified'),
(29, 'E11.65','Type 2 diabetes mellitus with hyperglycemia'),
(30, 'M79.3', 'Panniculitis, unspecified'),
(31, 'L40.0', 'Psoriasis vulgaris'),
(32, 'K57.30','Diverticulosis of large intestine without perforation'),
(33, 'Z00.00','Encounter for general adult medical examination'),
(34, 'J20.9', 'Acute bronchitis, unspecified'),
(35, 'R05',   'Cough'),
(36, 'R51',   'Headache'),
(37, 'R50.9', 'Fever, unspecified'),
(38, 'N20.0', 'Calculus of kidney'),
(39, 'K35.80','Acute appendicitis without abscess'),
(40, 'H35.30','Unspecified macular degeneration');

INSERT INTO Referral (
    referral_id, patient_id, department_id,
    referred_doctor_id, referring_doctor_id
)
SELECT
    s.i,
    (s.i % 2000000) + 1,
    (s.i % 150) + 1,
    CASE WHEN s.i % 3 != 0
         THEN CASE
                WHEN ((s.i % 2999) + 1) <> ((s.i % 3000) + 1)
                THEN ((s.i % 2999) + 1)
                ELSE ((s.i % 2999) + 2)
              END
         ELSE NULL
    END,
    (s.i % 3000) + 1
FROM generate_series(1, 3000000) AS s(i);

INSERT INTO Appointment (
    appointment_id, referral_id, doctor_id, patient_id,
    parent_appointment_id, prescription_value,
    appointment_date, status, appointment_type, priority_level
)
SELECT
    s.i,
    CASE WHEN s.i % 3 = 0 THEN (s.i % 3000000) + 1 ELSE NULL END,
    (s.i % 3000) + 1,
    (s.i % 2000000) + 1,
    CASE WHEN s.i % 5 = 0 AND s.i > 5
         THEN ((s.i - (s.i % 5)) % (s.i - 1)) + 1
         ELSE NULL
    END,
    (s.i % 2 = 0),
    (CURRENT_DATE - ((s.i % 1825) * INTERVAL '1 day'))::DATE,
    CASE (s.i % 10)
        WHEN 0 THEN 'SCHEDULED'  WHEN 1 THEN 'COMPLETED'
        WHEN 2 THEN 'COMPLETED'  WHEN 3 THEN 'COMPLETED'
        WHEN 4 THEN 'COMPLETED'  WHEN 5 THEN 'COMPLETED'
        WHEN 6 THEN 'COMPLETED'  WHEN 7 THEN 'CANCELLED'
        WHEN 8 THEN 'NO_SHOW'    ELSE       'IN_PROGRESS'
    END,
    CASE (s.i % 4)
        WHEN 0 THEN 'REGULAR'   WHEN 1 THEN 'FOLLOW_UP'
        WHEN 2 THEN 'EMERGENCY' ELSE       'CONTROL'
    END,
    CASE (s.i % 4)
        WHEN 0 THEN 'LOW'    WHEN 1 THEN 'MEDIUM'
        WHEN 2 THEN 'HIGH'   ELSE       'URGENT'
    END
FROM generate_series(1, 15000000) AS s(i);

INSERT INTO Medical_examination (
    exam_id, exam_date, notes, doctor_id, appointment_id
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    appointment_date,
    CASE (appointment_id % 5)
        WHEN 0 THEN 'Patient presents with reported symptoms. Examination conducted.'
        WHEN 1 THEN 'Follow-up examination. Patient condition stable.'
        WHEN 2 THEN 'Routine checkup. No acute distress noted.'
        WHEN 3 THEN 'Patient reports improvement since last visit. Vitals normal.'
        ELSE        'Comprehensive examination performed. Further tests ordered.'
    END,
    doctor_id,
    appointment_id
FROM Appointment
WHERE status = 'COMPLETED'
LIMIT 5000000;

INSERT INTO Patient_diagnosis (
    patient_diagnosis_id, patient_id, doctor_id,
    exam_id, icd_id, diagnosis_name,
    is_primary, date_from, date_to
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    a.patient_id,
    me.doctor_id,
    me.exam_id,
    (me.exam_id % 200) + 1,
    CASE (me.exam_id % 5)
        WHEN 0 THEN 'Hypertension stage 1'
        WHEN 1 THEN 'Type 2 Diabetes controlled'
        WHEN 2 THEN 'Anxiety disorder mild'
        WHEN 3 THEN 'Chronic back pain'
        ELSE        'Seasonal allergic rhinitis'
    END,
    (me.exam_id % 2 = 0),
    me.exam_date,
    CASE WHEN me.exam_id % 3 = 0
         THEN (me.exam_date + ((me.exam_id % 365 + 30) * INTERVAL '1 day'))::DATE
         ELSE NULL
    END
FROM Medical_examination me
JOIN Appointment a ON a.appointment_id = me.appointment_id
CROSS JOIN generate_series(1, 2) AS g(n)
LIMIT 8000000;

INSERT INTO Prescription (
    presc_id, atc_id, doctor_id, patient_id,
    presc_date, duration, dosage,
    inventory_id, patient_diagnosis_id
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    (pd.icd_id % 80) + 1,
    pd.doctor_id,
    pd.patient_id,
    pd.date_from,
    (pd.patient_diagnosis_id % 90) + 7,
    (pd.patient_diagnosis_id % 3) + 1,
    (pd.patient_diagnosis_id % (SELECT COUNT(*) FROM Inventory)::INT) + 1,
    pd.patient_diagnosis_id
FROM Patient_diagnosis pd
WHERE pd.is_primary = TRUE
LIMIT 4000000;

INSERT INTO Pharmacy_sale (
    sale_id, patient_id, pharmacy_id,
    presc_id, sale_date, total_amount
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    pr.patient_id,
    (pr.presc_id % 150) + 1,
    pr.presc_id,
    LEAST(
        (pr.presc_date + (pr.presc_id % 7 * INTERVAL '1 day'))::DATE,
        CURRENT_DATE
    ),
    round((d.unit_price * pr.dosage * 1.3)::NUMERIC, 2)
FROM Prescription pr
JOIN Inventory i  ON i.inventory_id = pr.inventory_id
JOIN Drug d       ON d.product_id   = i.product_id
LIMIT 3500000;

INSERT INTO Sale_item (sale_item_id, sale_id, presc_id, inventory_id, date)
SELECT
    ROW_NUMBER() OVER ()::INT,
    ps.sale_id,
    ps.presc_id,
    (ps.sale_id % (SELECT COUNT(*) FROM Inventory)::INT) + 1,
    ps.sale_date
FROM Pharmacy_sale ps
CROSS JOIN generate_series(1, 3) AS g(n)
LIMIT 10500000;

INSERT INTO Laboratory_test (
    lab_id, exam_id, patient_id, doctor_id,
    result, test_name, status
)
SELECT
    ROW_NUMBER() OVER ()::INT,
    me.exam_id,
    a.patient_id,
    me.doctor_id,
    CASE (me.exam_id % 4)
        WHEN 0 THEN 'Normal range'
        WHEN 1 THEN 'Slightly elevated'
        WHEN 2 THEN 'Below normal range'
        ELSE        'Pending review'
    END,
    CASE (me.exam_id % 20)
        WHEN 0  THEN 'Complete Blood Count'
        WHEN 1  THEN 'Comprehensive Metabolic Panel'
        WHEN 2  THEN 'Lipid Panel'
        WHEN 3  THEN 'HbA1c'
        WHEN 4  THEN 'Thyroid Function Test'
        WHEN 5  THEN 'Urinalysis'
        WHEN 6  THEN 'Blood Culture'
        WHEN 7  THEN 'PT/INR'
        WHEN 8  THEN 'ESR'
        WHEN 9  THEN 'CRP'
        WHEN 10 THEN 'Liver Function Test'
        WHEN 11 THEN 'Renal Function Test'
        WHEN 12 THEN 'Electrolytes'
        WHEN 13 THEN 'Glucose Fasting'
        WHEN 14 THEN 'HIV Antibody'
        WHEN 15 THEN 'Hepatitis B Surface Antigen'
        WHEN 16 THEN 'Chest X-Ray'
        WHEN 17 THEN 'ECG'
        WHEN 18 THEN 'MRI Brain'
        ELSE        'CT Abdomen'
    END,
    CASE (me.exam_id % 4)
        WHEN 0 THEN 'PENDING'
        WHEN 1 THEN 'IN_PROGRESS'
        ELSE        'COMPLETED'
    END
FROM Medical_examination me
JOIN Appointment a ON a.appointment_id = me.appointment_id
CROSS JOIN generate_series(1, 2) AS g(n)
LIMIT 10000000;

DROP TABLE IF EXISTS tmp_names;
DROP TABLE IF EXISTS tmp_surnames;